﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;

namespace JX.Infrastructure.Common
{
	/// <summary>
	/// Excel帮助类
	/// </summary>
	public class ExcelHelper
	{
		/// <summary>
		/// 上传EXCEL文件到服务器，上传成功后，上传完整路径通过ResultInfo.Data返回
		/// </summary>
		/// <param name="fileDir">上传文件保存目录名，默认为“Temp”</param>
		/// <returns></returns>
		public static async Task<ResultInfo> UploadExcel(string fileDir = "Temp")
		{
			ResultInfo resultInfo = new ResultInfo();
			if (MyHttpContext.Current.Request.Form.Files.Count > 0)
			{
				var uploadFile = MyHttpContext.Current.Request.Form.Files[0];
				if (uploadFile != null && uploadFile.Length > 0)
				{
					string fileExt = Path.GetExtension(uploadFile.FileName).ToLower();
					string checkStr = "xls|xlsx";
					//检查上传文件后缀名
					if (StringHelper.FoundCharInArr(checkStr, fileExt.Replace(".", ""), "|"))
					{
						#region 得到上传目录名
						if (string.IsNullOrWhiteSpace(fileDir))
						{
							fileDir = "Temp";
						}
						switch (FileHelper.DirectorySeparatorChar)
						{
							case "/"://Mac OS and Linux
								fileDir = fileDir.Replace("\\", FileHelper.DirectorySeparatorChar);
								break;
							case "\\"://WINDOWS
								fileDir = fileDir.Replace("/", FileHelper.DirectorySeparatorChar);
								break;
						}
						if (fileDir.Substring(fileDir.Length - 1, 1) != FileHelper.DirectorySeparatorChar)
						{
							fileDir = fileDir + FileHelper.DirectorySeparatorChar;
						}
						string filePath = FileHelper.CreateFileFolder(FileHelper.WebRootPath + FileHelper.DirectorySeparatorChar + fileDir);
						#endregion
						string fileName = DataSecurity.MakeFileRndName();
						filePath = filePath + fileName + fileExt;
						using (var stream = new FileStream(filePath, FileMode.Create))
						{
							await uploadFile.CopyToAsync(stream);
						}
						resultInfo.Status = 1;
						resultInfo.Msg = "上传成功！";
						resultInfo.Data = filePath;
					}
					else
					{
						resultInfo.Status = 0;
						resultInfo.Msg = "只能上传" + checkStr + "后缀的文件！";
					}
				}
				else
				{
					resultInfo.Status = 0;
					resultInfo.Msg = "上传失败！上传文件的大小为0！";
				}
			}
			else
			{
				resultInfo.Status = 0;
				resultInfo.Msg = "上传失败！上传组件不存在！";
			}
			return resultInfo;
		}
		/// <summary>
		/// Excel导入成Datable
		/// </summary>
		/// <param name="file">导入路径(包含文件名与扩展名)。例：d:\test.xlsx</param>
		/// <returns></returns>
		public static DataTable ExcelToTable(string file)
		{
			DataTable dt = new DataTable();
			IWorkbook workbook;
			string fileExt = Path.GetExtension(file).ToLower();
			using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
			{
				//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
				if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); }
				else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); }
				else { workbook = null; }
				if (workbook == null) { return null; }
				ISheet sheet = workbook.GetSheetAt(0);

				//表头  
				IRow header = sheet.GetRow(sheet.FirstRowNum);
				List<int> columns = new List<int>();
				for (int i = 0; i < header.LastCellNum; i++)
				{
					object obj = GetValueType(header.GetCell(i));
					if (obj == null || obj.ToString() == string.Empty)
					{
						dt.Columns.Add(new DataColumn("Col" + i.ToString()));
					}
					else
						dt.Columns.Add(new DataColumn(obj.ToString()));
					columns.Add(i);
				}
				//数据  
				for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
				{
					DataRow dr = dt.NewRow();
					bool hasValue = false;
					foreach (int j in columns)
					{
						dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
						if (dr[j] != null && dr[j].ToString() != string.Empty)
						{
							hasValue = true;
						}
					}
					if (hasValue)
					{
						dt.Rows.Add(dr);
					}
				}
			}
			return dt;
		}

		/// <summary>
		/// Datable导出成Excel，文件保存在服务器本地
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="file">导出路径(包括文件名与扩展名)。例：d:\test.xlsx</param>
		public static void TableToExcel(DataTable dt, string file)
		{
			bool isXlsx = true;
			string fileExt = Path.GetExtension(file).ToLower();
			if (fileExt == ".xlsx") { isXlsx = true; }
			else if (fileExt == ".xls") { isXlsx = false; }
			var buf = TableToExcel(dt,isXlsx);
			if (buf == null)
				return;

			//保存为Excel文件  
			using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
			{
				fs.Write(buf, 0, buf.Length);
				fs.Flush();
			}
		}
		/// <summary>
		/// Datable导出成Excel，返回byte[]，方便在控制器调用方法后返回一个文件给客户端。
		/// 例：var buffer=TableToExcel(dt); return File(buffer, "application/ms-excel", "list.xlsx"); 
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="isXlsx">导出文件是否xlsx后缀名</param>
		public static byte[] TableToExcel(DataTable dt, bool isXlsx=true)
		{
			IWorkbook workbook;
			if (isXlsx) { workbook = new XSSFWorkbook(); }//xlsx
			else { workbook = new HSSFWorkbook(); }//xls
			if (workbook == null) { return null; }
			ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

			ICellStyle cellStyle = workbook.CreateCellStyle();//声明样式
			cellStyle.Alignment = HorizontalAlignment.Center;//水平居中
			cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
			IFont font = workbook.CreateFont();//声明字体
			//font.Boldweight = (Int16)FontBoldWeight.Bold;//加粗
			font.FontHeightInPoints = 12;//字体大小
			cellStyle.SetFont(font);//加入单元格

			//表头  
			IRow row = sheet.CreateRow(0);
			for (int i = 0; i < dt.Columns.Count; i++)
			{
				ICell cell = row.CreateCell(i);
				cell.SetCellValue(dt.Columns[i].ColumnName);
				cell.CellStyle = cellStyle;//设置样式
			}
			//合并单元格（第几行，到第几行，第几列，到第几列）
			//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));

			//数据  
			for (int i = 0; i < dt.Rows.Count; i++)
			{
				IRow row1 = sheet.CreateRow(i + 1);
				for (int j = 0; j < dt.Columns.Count; j++)
				{
					ICell cell = row1.CreateCell(j);
					cell.SetCellValue(dt.Rows[i][j].ToString());
				}
			}

			//列宽自适应，只对英文和数字有效
			for (int i = 0; i <= dt.Columns.Count; i++)
			{
				sheet.AutoSizeColumn(i);
			}
			//获取当前列的宽度，然后对比本列的长度，取最大值
			for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
			{
				int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
				for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
				{
					IRow currentRow;
					//当前行未被使用过
					if (sheet.GetRow(rowNum) == null)
					{
						currentRow = sheet.CreateRow(rowNum);
					}
					else
					{
						currentRow = sheet.GetRow(rowNum);
					}

					if (currentRow.GetCell(columnNum) != null)
					{
						ICell currentCell = currentRow.GetCell(columnNum);
						int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
						if (columnWidth < length)
						{
							columnWidth = length;
						}
					}
				}
				sheet.SetColumnWidth(columnNum, columnWidth * 256);
			}

			//转为字节数组
			byte[] buffer = null;
			using (MemoryStream ms = new MemoryStream())
			{
				workbook.Write(ms);
				buffer = ms.GetBuffer();
				ms.Close();
			}
			return buffer;

		}

		/// <summary>
		/// 获取单元格类型
		/// </summary>
		/// <param name="cell"></param>
		/// <returns></returns>
		public static object GetValueType(ICell cell)
		{
			if (cell == null)
				return null;
			switch (cell.CellType)
			{
				case CellType.Blank: //BLANK:  
					return null;
				case CellType.Boolean: //BOOLEAN:  
					return cell.BooleanCellValue;
				case CellType.Numeric: //NUMERIC:  
					if (DateUtil.IsCellDateFormatted(cell))
					{
						return cell.DateCellValue.ToString();
					}
					return cell.NumericCellValue;
				case CellType.String: //STRING:  
					return cell.StringCellValue;
				case CellType.Error: //ERROR:  
					return cell.ErrorCellValue;
				case CellType.Formula: //FORMULA:  
				default:
					return "=" + cell.CellFormula;
			}
		}

	}
}
